﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
using INTEX.DataBase.Tools;
namespace WPFExcelReport
{

    public class FLTCOTSData : BaseFunction
    {
        
        public FLTCOTSData()
            : base()
        {

        }
        public FLTCOTSData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetVKPOS(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "VKPOS";
            string ORDER = dwABPOS["ORDER"].ToString().Trim();

            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT A.VKPKDKOMM,TO_NUMBER(A.VKPAUFLFD) AS VKPAUFLFD,A.VKPAUFPOS,A.VKPKUNDNR,A.VKPSACHBE,A.VKPKOLL, "
                    + " A.VKPART,A.VKPFARBE,A.VKPMENGE1*0.01 AS QTY,A.VKPZUGET*0.01 AS DNQTY,A.VKPMEEH, "
                    + " A.VKPLIEFTERM,A.VKPNEUDAT,'' AS REMARK,B.VAFPART,B.VAFPVAR||' ' AS VAFPVAR,B.VAFFFARBE  "
                    + " FROM VKPOS A,VAFARBEN B "
                    + " WHERE A.VKPMANDANT = B.VAFMANDANT AND A.VKPKOLL = B.VAFKOLL "
                    + " AND A.VKPART = B.VAFART AND A.VKPFARBE = B.VAFFARBE "
                    + " AND A.VKPMANDANT = '00' AND A.VKPSA <>'AJ' "
                    + " AND A.VKPAUFKRS = '  ' AND to_number(A.VKPAUFLFD) = '" + ORDER + "' "
                    + " ORDER BY VKPAUFPOS "
                    ;
                
                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        /*b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["VKPKDARTNR"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["VKPKDARTNR"] = value;
                         */
                        GetREMARK(dw);
                    }
                }                
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        public DataSet GetABPOS(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "ABPOS";
            string ORDER = dwABPOS["VKPAUFLFD"].ToString().Trim();
            string ARTICLE = dwABPOS["VAFPART"].ToString();
            string VAR = dwABPOS["VAFPVAR"].ToString();
            string COLOR = dwABPOS["VAFFFARBE"].ToString();

            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = " SELECT TRIM(A.EKKKNTRNRL)||'/'||TRIM(A.EKKAKKRED) AS CO, "
                    + " DECODE(A.EKKAUFART,'BE','Purchase Order','unkown') AS ORDERTYP, "
                    + " A.EKKLIEFNR AS SUPCODE,C.LSNAME1 AS SUPPLIER,B.EKPAUFKRS AS KRS,B.EKPAUFLFD AS FLFD, "
                    + " B.EKPAUFPOS AS POS,B.EKPNEUD AS ENTERDATE, "
                    + " B.EKPNEUB AS STAFF,B.EKPART AS ARTICLE,B.EKPVAR AS VAR,B.EKPFARBE AS FARBE, "
                    + " EKPMEEH AS UNIT,EKPME1*0.01 AS QTY,0 AS ALLQTY,0 AS BATQTY,EKPME3*0.01 AS REQTY, "
                    + " B.EKPWUTERM AS FDATE,B.EKPWVTERM AS EDATE,0 AS ENDDATE,0 AS LASTDATE, "
                    + " TO_NUMBER(NVL(TRIM(substr(EKPFILLER14,1,8)),'0')) AS RECDATE "
                    + " FROM EKKOPF A,EKPOS B,LIEFDAT C  "
                    + " WHERE A.EKKMANDANT = B.EKPMANDANT AND A.EKKAUFKRS = B.EKPAUFKRS "
                    + " AND A.EKKAUFLFD = B.EKPAUFLFD AND A.EKKMANDANT = C.LSMANDANT "
                    + " AND A.EKKLIEFNR = C.LSLIEFNR "
                    + " AND (TRIM(A.EKKKNTRNRL) ='" + ORDER + "' "
                    + " OR SUBSTR(TRIM(A.EKKKNTRNRL),1,INSTR(TRIM(A.EKKKNTRNRL),'-')-1) ='" + ORDER + "' "
                    + " OR SUBSTR(TRIM(A.EKKKNTRNRL),1,INSTR(TRIM(A.EKKKNTRNRL),'/')-1) ='" + ORDER + "' "
                    + " OR SUBSTR(TRIM(A.EKKKNTRNRL),INSTR(TRIM(A.EKKKNTRNRL),'/',1)+1) ='" + ORDER + "' "
                    + " OR SUBSTR(SUBSTR(TRIM(A.EKKKNTRNRL),INSTR(TRIM(A.EKKKNTRNRL),'/',1)+1),1,  "
                    + "    INSTR(SUBSTR(TRIM(A.EKKKNTRNRL),INSTR(TRIM(A.EKKKNTRNRL),'/',1)+1),'/',1)-1)='" + ORDER + "' "
                    + " OR SUBSTR(SUBSTR(TRIM(A.EKKKNTRNRL),INSTR(TRIM(A.EKKKNTRNRL),'/',1)+1),1,  "
                    + "    INSTR(SUBSTR(TRIM(A.EKKKNTRNRL),INSTR(TRIM(A.EKKKNTRNRL),'/',1)+1),'-',1)-1)='" + ORDER + "' "
                    + " OR SUBSTR(TRIM(A.EKKKNTRNRL),INSTR(TRIM(A.EKKKNTRNRL),'/',1,2)+1)='" + ORDER + "' "
                    + " OR SUBSTR(SUBSTR(TRIM(A.EKKKNTRNRL),INSTR(TRIM(A.EKKKNTRNRL),'/',1,2)+1),1, "
                    + "     INSTR(SUBSTR(TRIM(A.EKKKNTRNRL),INSTR(TRIM(A.EKKKNTRNRL),'/',1,2)+1),'-',1)-1)  ='" + ORDER + "' "
                    + " OR SUBSTR(SUBSTR(TRIM(A.EKKKNTRNRL),INSTR(TRIM(A.EKKKNTRNRL),'/',1,2)+1),1, "
                    + "     INSTR(SUBSTR(TRIM(A.EKKKNTRNRL),INSTR(TRIM(A.EKKKNTRNRL),'/',1,2)+1),'/',1)-1) ='" + ORDER + "' "
                    + " OR SUBSTR(TRIM(A.EKKKNTRNRL),INSTR(TRIM(A.EKKKNTRNRL),'/',1,3)+1) ='" + ORDER + "' "
                    + " OR TRIM(A.EKKAKKRED) ='" + ORDER + "' "
                    + " OR SUBSTR(TRIM(A.EKKAKKRED),1,INSTR(TRIM(A.EKKAKKRED),'-')-1) ='" + ORDER + "' "
                    + " OR SUBSTR(TRIM(A.EKKAKKRED),1,INSTR(TRIM(A.EKKAKKRED),'/')-1) ='" + ORDER + "' "
                    + " OR SUBSTR(TRIM(A.EKKAKKRED),INSTR(TRIM(A.EKKAKKRED),'/',1)+1) ='" + ORDER + "' "
                    + " OR SUBSTR(SUBSTR(TRIM(A.EKKAKKRED),INSTR(TRIM(A.EKKAKKRED),'/',1)+1),1,  "
                    + "   INSTR(SUBSTR(TRIM(A.EKKAKKRED),INSTR(TRIM(A.EKKAKKRED),'/',1)+1),'/',1)-1) ='" + ORDER + "' "
                    + " OR SUBSTR(SUBSTR(TRIM(A.EKKAKKRED),INSTR(TRIM(A.EKKAKKRED),'/',1)+1),1,  "
                    + "    INSTR(SUBSTR(TRIM(A.EKKAKKRED),INSTR(TRIM(A.EKKAKKRED),'/',1)+1),'-',1)-1) ='" + ORDER + "' "
                    + " OR SUBSTR(TRIM(A.EKKAKKRED),INSTR(TRIM(A.EKKAKKRED),'/',1,2)+1)='" + ORDER + "' "
                    + " OR SUBSTR(SUBSTR(TRIM(A.EKKAKKRED),INSTR(TRIM(A.EKKAKKRED),'/',1,2)+1),1, "
                    + "     INSTR(SUBSTR(TRIM(A.EKKAKKRED),INSTR(TRIM(A.EKKAKKRED),'/',1,2)+1),'-',1)-1) ='" + ORDER + "' "
                    + " OR SUBSTR(SUBSTR(TRIM(A.EKKAKKRED),INSTR(TRIM(A.EKKAKKRED),'/',1,2)+1),1, "
                    + "     INSTR(SUBSTR(TRIM(A.EKKAKKRED),INSTR(TRIM(A.EKKAKKRED),'/',1,2)+1),'/',1)-1) ='" + ORDER + "' "
                    + " OR SUBSTR(TRIM(A.EKKAKKRED),INSTR(TRIM(A.EKKAKKRED),'/',1,3)+1) ='" + ORDER + "' "
                    + " ) AND B.EKPSA='PO' AND (B.EKPART,B.EKPVAR,B.EKPFARBE) IN "
                    + " (SELECT A.GAGART,A.GAGVAR,A.GAGFARBE FROM GASTGA A "
                    + " WHERE A.GAGART = '" + ARTICLE + "' AND A.GAGVAR ='" + VAR + "' "
                    + " AND A.GAGFARBE ='" + COLOR + "'"
                    + " UNION ALL "
                    + " SELECT A.GAGGART,A.GAGGVAR,A.GAGGFARBE "
                    + " FROM GASTGA A CONNECT BY PRIOR A.GAGGART = A.GAGART AND PRIOR A.GAGGVAR = A.GAGVAR "
                    + " AND PRIOR A.GAGGFARBE = A.GAGFARBE "
                    + " START WITH A.GAGART = '"+ARTICLE+"' AND A.GAGVAR ='"+VAR+"' "
                    + " AND A.GAGFARBE ='"+COLOR+"') "
                    + " UNION ALL "
                    + " SELECT A.VVTKDAUFTNR AS CO,DECODE(A.VVTAUFTYP,'L','Delivery Note','UNKOWN') AS ORDERTYP, "
                    + " B.KDAADRSL AS SUPCODE,B.KDANAME1 AS SUPPLIER,A.VVTAUFKRS AS KRS, "
                    + " A.VVTAUFLFD AS FLFD,A.VVTAUFPOS AS POS,C.VVNEUDAT AS ENTERDATE, "
                    + " C.VVNEUBEN AS STAFF,A.VVTART AS ARTICLE,A.VVTVAR AS VAR, "
                    + " A.VVTFARBE AS FARBE,'KG' AS UNIT,A.VVTKG*0.001 AS QTY,0 AS ALLQTY, "
                    + " 0 AS BATQTY,A.VVTRUECKKG*0.001 AS REQTY, "
                    + " C.VVLIEFDAT AS FDATE,0 AS EDATE,C.VVVERSDAT AS ENDDATE,0 AS LASTDATE,0 AS RECDATE "
                    + " FROM VVTLIEF A,KUNDADR B,VVLIEF C  "
                    + " WHERE A.VVTMANDANT = C.VVMANDANT AND A.VVTLIEFSCH = C.VVLIEFSCH "
                    + " AND A.VVTMANDANT = B.KDAMANDANT "
                    + " AND A.VVTKUNDNRL = B.KDAKUNDNR AND A.VVTADRSLL = B.KDAADRSL "
                    + " AND (TRIM(A.VVTKDAUFTNR) ='" + ORDER + "' "
                    + " OR SUBSTR(TRIM(A.VVTKDAUFTNR),1,INSTR(TRIM(A.VVTKDAUFTNR),'-')-1) ='" + ORDER + "' "
                    + " OR SUBSTR(TRIM(A.VVTKDAUFTNR),1,INSTR(TRIM(A.VVTKDAUFTNR),'/')-1) ='" + ORDER + "' "
                    + " OR SUBSTR(TRIM(A.VVTKDAUFTNR),INSTR(TRIM(A.VVTKDAUFTNR),'/',1)+1) ='" + ORDER + "' "
                    + " OR SUBSTR(SUBSTR(TRIM(A.VVTKDAUFTNR),INSTR(TRIM(A.VVTKDAUFTNR),'/',1)+1),1,  "
                    + "    INSTR(SUBSTR(TRIM(A.VVTKDAUFTNR),INSTR(TRIM(A.VVTKDAUFTNR),'/',1)+1),'/',1)- 1) ='" + ORDER + "' "
                    + " OR SUBSTR(SUBSTR(TRIM(A.VVTKDAUFTNR),INSTR(TRIM(A.VVTKDAUFTNR),'/',1)+1),1, "
                    + "    INSTR(SUBSTR(TRIM(A.VVTKDAUFTNR),INSTR(TRIM(A.VVTKDAUFTNR),'/',1)+1),'-',1)-1) ='" + ORDER + "' "
                    + " OR SUBSTR(TRIM(A.VVTKDAUFTNR),INSTR(TRIM(A.VVTKDAUFTNR),'/',1,2)+1)='" + ORDER + "' "
                    + " OR SUBSTR(SUBSTR(TRIM(A.VVTKDAUFTNR),INSTR(TRIM(A.VVTKDAUFTNR),'/',1,2)+1),1, "
                    + "     INSTR(SUBSTR(TRIM(A.VVTKDAUFTNR),INSTR(TRIM(A.VVTKDAUFTNR),'/',1,2)+1),'-',1)-1) ='" + ORDER + "' "
                    + " OR SUBSTR(SUBSTR(TRIM(A.VVTKDAUFTNR),INSTR(TRIM(A.VVTKDAUFTNR),'/',1,2)+1),1, "
                    + "     INSTR(SUBSTR(TRIM(A.VVTKDAUFTNR),INSTR(TRIM(A.VVTKDAUFTNR),'/',1,2)+1),'/',1)-1) ='" + ORDER + "' "
                    + " OR SUBSTR(TRIM(A.VVTKDAUFTNR),INSTR(TRIM(A.VVTKDAUFTNR),'/',1,3)+1) ='" + ORDER + "' ) "
                    + " AND (A.VVTART,A.VVTVAR,A.VVTFARBE) IN "
                    + " (SELECT A.GAGART,A.GAGVAR,A.GAGFARBE FROM GASTGA A "
                    + " WHERE A.GAGART = '" + ARTICLE + "' AND A.GAGVAR ='" + VAR + "' "
                    + " AND A.GAGFARBE ='" + COLOR + "'"
                    + " UNION ALL "
                    + " SELECT A.GAGGART,A.GAGGVAR,A.GAGGFARBE "
                    + " FROM GASTGA A CONNECT BY PRIOR A.GAGGART = A.GAGART AND PRIOR A.GAGGVAR = A.GAGVAR "
                    + " AND PRIOR A.GAGGFARBE = A.GAGFARBE "
                    + " START WITH A.GAGART = '" + ARTICLE + "' AND A.GAGVAR ='" + VAR + "' "
                    + " AND  A.GAGFARBE ='" + COLOR + "') "
                    + " UNION ALL "
                    + " SELECT TRIM(A.ABKKDAUFTNR)||'/'||TRIM(A.ABKKDZEICH) AS CO, "
                    + " DECODE(A.ABKAUFKRS,'D7','Dyeing Order','SP','Spinning Order', "
                    + " 'ES','External Spinning','  ','Production Order') AS ORDERTYP, "
                    + " NVL(TRIM(B.ABPLIEFNR),'Internal Production') AS SUPCODE, "
                    + " nvl(trim(C.LSNAME1),'Filpucci (Zhangjiagang) Special Textiles') AS SUPPLIER,B.ABPAUFKRS AS KRS, "
                    + " B.ABPAUFLFD AS FLFD,B.ABPAUFPOS AS POS,B.ABPNEUDAT AS ENTERDATE, "
                    + " A.ABKARTSB AS STAFF,B.ABPART AS ARTICLE,B.ABPVAR AS VAR,B.ABPFARBE AS FARBE, "
                    + " 'KG' AS UNIT,B.ABPSOLLMENGE5*0.01 AS QTY,B.ABPZUGKG*0.01 AS ALLQTY, "
                    + " (D.ABASGESGEW-D.ABARSTSGEW)*0.001 AS BATQTY, "
                    + " B.ABPEAKG*0.01 AS REQTY,B.ABPSTARTDAT AS FDATE,B.ABPENDDAT AS EDATE, "
                    + " B.ABPERLDAT AS ENDDATE,E.LASTDATE AS LASTDATE,F.ABFRUECKDAT AS RECDATE "
                    + " FROM ABKOPF A,ABPOS B,LIEFDAT C,ABAUFM D, "
                    + " (SELECT ABAAUFKRS,ABAAUFLFD,ABAAUFPOS,MAX(ABANEUDAT) AS LASTDATE  "
                    + " FROM ABAUFM A WHERE A.ABAAPOS>'0001' "
                    + " GROUP BY ABAAUFKRS,ABAAUFLFD,ABAAUFPOS) E,ABFOLGE F "
                    + " WHERE A.ABKMANDANT = B.ABPMANDANT AND A.ABKAUFKRS = B.ABPAUFKRS "
                    + " AND A.ABKAUFLFD = B.ABPAUFLFD AND B.ABPMANDANT = C.LSMANDANT(+) "
                    + " AND B.ABPLIEFNR = C.LSLIEFNR(+) AND B.ABPMANDANT = D.ABAMANDANT "
                    + " AND B.ABPAUFKRS = D.ABAAUFKRS AND B.ABPAUFLFD = D.ABAAUFLFD "
                    + " AND B.ABPAUFPOS = D.ABAAUFPOS AND B.ABPAUFKRS = E.ABAAUFKRS(+) "
                    + " AND B.ABPAUFLFD = E.ABAAUFLFD(+) AND B.ABPAUFPOS = E.ABAAUFPOS(+) "
                    + " AND B.ABPMANDANT = F.ABFMANDANT AND B.ABPAUFKRS = F.ABFAUFKRS "
                    + " AND B.ABPAUFLFD = F.ABFAUFLFD AND B.ABPAUFPOS = F.ABFAUFPOS "
                    + " AND F.ABFKST = '0122' AND D.ABAAUFTYP = 'V' AND D.ABAAPOS = '0001' "
                    + " AND (TRIM(A.ABKKDAUFTNR) ='" + ORDER + "' "
                    + " OR SUBSTR(TRIM(A.ABKKDAUFTNR),1,INSTR(TRIM(A.ABKKDAUFTNR),'-')-1) ='" + ORDER + "' "
                    + " OR SUBSTR(TRIM(A.ABKKDAUFTNR),1,INSTR(TRIM(A.ABKKDAUFTNR),'/')-1) ='" + ORDER + "' "
                    + " OR SUBSTR(TRIM(A.ABKKDAUFTNR),INSTR(TRIM(A.ABKKDAUFTNR),'/',1)+1) ='" + ORDER + "' "
                    + " OR SUBSTR(SUBSTR(TRIM(A.ABKKDAUFTNR),INSTR(TRIM(A.ABKKDAUFTNR),'/',1)+1),1,  "
                    + "    INSTR(SUBSTR(TRIM(A.ABKKDAUFTNR),INSTR(TRIM(A.ABKKDAUFTNR),'/',1)+1),'/',1)-1) ='" + ORDER + "' "
                    + " OR SUBSTR(SUBSTR(TRIM(A.ABKKDAUFTNR),INSTR(TRIM(A.ABKKDAUFTNR),'/',1)+1),1, "
                    + "    INSTR(SUBSTR(TRIM(A.ABKKDAUFTNR),INSTR(TRIM(A.ABKKDAUFTNR),'/',1)+1),'-',1)-1) ='" + ORDER + "' "
                    + " OR SUBSTR(TRIM(A.ABKKDAUFTNR),INSTR(TRIM(A.ABKKDAUFTNR),'/',1,2)+1)='" + ORDER + "' "
                    + " OR SUBSTR(SUBSTR(TRIM(A.ABKKDAUFTNR),INSTR(TRIM(A.ABKKDAUFTNR),'/',1,2)+1),1, "
                    + "     INSTR(SUBSTR(TRIM(A.ABKKDAUFTNR),INSTR(TRIM(A.ABKKDAUFTNR),'/',1,2)+1),'-',1)-1) ='" + ORDER + "' "
                    + " OR SUBSTR(SUBSTR(TRIM(A.ABKKDAUFTNR),INSTR(TRIM(A.ABKKDAUFTNR),'/',1,2)+1),1, "
                    + "     INSTR(SUBSTR(TRIM(A.ABKKDAUFTNR),INSTR(TRIM(A.ABKKDAUFTNR),'/',1,2)+1),'/',1)-1) ='" + ORDER + "' "
                    + " OR SUBSTR(TRIM(A.ABKKDAUFTNR),INSTR(TRIM(A.ABKKDAUFTNR),'/',1,3)+1) ='" + ORDER + "' "
                    + " OR TRIM(A.ABKKDZEICH) ='" + ORDER + "' "
                    + " OR SUBSTR(TRIM(A.ABKKDZEICH),1,INSTR(TRIM(A.ABKKDZEICH),'-')-1) ='" + ORDER + "' "
                    + " OR SUBSTR(TRIM(A.ABKKDZEICH),1,INSTR(TRIM(A.ABKKDZEICH),'/')-1) ='" + ORDER + "' "
                    + " OR SUBSTR(TRIM(A.ABKKDZEICH),INSTR(TRIM(A.ABKKDZEICH),'/',1)+1) ='" + ORDER + "' "
                    + " OR SUBSTR(SUBSTR(TRIM(A.ABKKDZEICH),INSTR(TRIM(A.ABKKDZEICH),'/',1)+1),1,  "
                    + "   INSTR(SUBSTR(TRIM(A.ABKKDZEICH),INSTR(TRIM(A.ABKKDZEICH),'/',1)+1),'/',1)-1) ='" + ORDER + "' "
                    + " OR SUBSTR(SUBSTR(TRIM(A.ABKKDZEICH),INSTR(TRIM(A.ABKKDZEICH),'/',1)+1),1, "
                    + "    INSTR(SUBSTR(TRIM(A.ABKKDZEICH),INSTR(TRIM(A.ABKKDZEICH),'/',1)+1),'-',1)-1) ='" + ORDER + "' "
                    + " OR SUBSTR(TRIM(A.ABKKDZEICH),INSTR(TRIM(A.ABKKDZEICH),'/',1,2)+1)='" + ORDER + "' "
                    + " OR SUBSTR(SUBSTR(TRIM(A.ABKKDZEICH),INSTR(TRIM(A.ABKKDZEICH),'/',1,2)+1),1, "
                    + "     INSTR(SUBSTR(TRIM(A.ABKKDZEICH),INSTR(TRIM(A.ABKKDZEICH),'/',1,2)+1),'-',1)-1) ='" + ORDER + "' "
                    + " OR SUBSTR(SUBSTR(TRIM(A.ABKKDZEICH),INSTR(TRIM(A.ABKKDZEICH),'/',1,2)+1),1, "
                    + "     INSTR(SUBSTR(TRIM(A.ABKKDZEICH),INSTR(TRIM(A.ABKKDZEICH),'/',1,2)+1),'/',1) -1) ='" + ORDER + "' "
                    + " OR SUBSTR(TRIM(A.ABKKDZEICH),INSTR(TRIM(A.ABKKDZEICH),'/',1,3)+1) ='" + ORDER + "' ) "
                    + " and B.ABPLOEKZ = ' 'AND (B.ABPART,B.ABPVAR,B.ABPFARBE) IN "
                    + " (SELECT A.GAGART,A.GAGVAR,A.GAGFARBE FROM GASTGA A "
                    + " WHERE A.GAGART = '" + ARTICLE + "' AND A.GAGVAR ='" + VAR + "' "
                    + " AND A.GAGFARBE ='" + COLOR + "'"
                    + " UNION ALL "
                    + " SELECT A.GAGGART,A.GAGGVAR,A.GAGGFARBE "
                    + " FROM GASTGA A CONNECT BY PRIOR A.GAGGART = A.GAGART AND PRIOR A.GAGGVAR = A.GAGVAR "
                    + " AND PRIOR A.GAGGFARBE = A.GAGFARBE "
                    + " START WITH A.GAGART = '" + ARTICLE + "' AND A.GAGVAR ='" + VAR + "' "
                    + " AND A.GAGFARBE ='" + COLOR + "') "
                    + " ORDER BY ENTERDATE,FLFD,POS "

                    //+ " AND trim(C.WAFART)||'-'||TRIM(C.WAFZUSATZ) = '" + ORDER + "' "
                    //+ " ORDER BY 1,2,3 "
                    ;

                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["SUPPLIER"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["SUPPLIER"] = value;
                    }
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        public DataSet GetREMARK(DataRow dwUI)
        {
            string TableName = "REMARK";
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tableLocal = null;
            string VKPAUFLFD = dwUI["VKPAUFLFD"].ToString();
            string VKPAUFPOS = dwUI["VKPAUFPOS"].ToString();
            try
            {

                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = " SELECT SUBSTR(A.TXTISIND,9,3) AS FPOS,B.TXTKTEXT FROM TXTIND A,TXTKON B "
                    + " WHERE A.TXTIMANDANT = B.TXTKMANDANT "
                    + " AND A.TXTIPIND = B.TXTKPIND "
                    + " AND SUBSTR(A.TXTISIND,1,2) = '  ' "
                    + " AND TO_NUMBER(SUBSTR(A.TXTISIND,3,6)) = '"+VKPAUFLFD+"' "
                    + " AND (SUBSTR(A.TXTISIND,9,3) = '" + VKPAUFPOS + "' OR SUBSTR(A.TXTISIND,9,3) = '000') "
                    + " AND SUBSTR(A.TXTISIND,12,4) = 'IVDI' "
                    + " ORDER BY 1 "
                    ;

                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = TableName;
                    tableLocal = setResult.Tables[0];
                    tableLocal.TableName = TableName;
                    byte[] b;
                    string value;
                    string sTemp = "";
                    string Column = "";
                    string remark = "";
                    foreach (DataRow dwTemp in tableLocal.Rows)
                    {
                        Column = "TXTKTEXT";
                        #region
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dwTemp[Column].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        remark = remark+ value + Environment.NewLine;
                        #endregion


                    }
                    dwUI["REMARK"] = remark;
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet BuildFLTCOTSData(DataRow dwUI)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetVKPOS(dwUI);
                setReturn.Merge(setResult);
                /*setResult = GetABPOS(dwUI);
                setReturn.Merge(setResult);*/
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        
    }
}